package org.compiere.report;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.compiere.model.MAssetTransaction;
import org.compiere.model.MAssetTransactionLine;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.X_A_Asset;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.utils.CopyRow;
import org.utils.fwMoney;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 *
 */
public class A_Asset_Form_OS4 extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_A_Asset_Transaction_ID;
	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("A_Asset_Transaction_ID")) 
				p_A_Asset_Transaction_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "A_Asset_Form_OS4_temp2.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
	
		MOrg org = MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx));
		MBPartner bpartner = MBPartner.get(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()));
		
		R = sheet.getRow(9);
		C = R.getCell(44);
		C.setCellValue((bpartner.get_Value("FI_TIN").toString() == null ? " ": bpartner.get_Value("FI_TIN").toString()));
		R = sheet.getRow(10);
		C = R.getCell(0);
		C.setCellValue((org.getName() == null ? " " : org.getName()));
		MAssetTransaction assetTransac = new MAssetTransaction(m_ctx, p_A_Asset_Transaction_ID, get_TrxName());
		
		R = sheet.getRow(20);
		C = R.getCell(41);
		C.setCellValue((assetTransac.getDocumentNo() == null ? " ": assetTransac.getDocumentNo()));
		C = R.getCell(49);
		C.setCellValue(assetTransac.getDateAcct() == null ? " " : new SimpleDateFormat("dd.MM.yyyy").format(assetTransac.getDateAcct()));
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal total = new BigDecimal(0.0);
		int row = 24;
		String sql =" SELECT A_Asset_TransactionLine_ID FROM A_Asset_TransactionLine  "
				+ " WHERE AD_Client_ID = " + Env.getAD_Client_ID(m_ctx)
				+ " AND AD_Org_ID = " + Env.getAD_Org_ID(m_ctx)
				+ " AND A_Asset_Transaction_ID = " + p_A_Asset_Transaction_ID
				+ " ORDER BY A_Asset_ID ";
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int iAssetTransactionLine_ID = 0,nn = 0;
			CopyRow copyRows = new CopyRow();
			while(rs.next())
			{
				nn++; row++;
				
				copyRows.copyRow(wb, sheet, row, row+1);
				
				iAssetTransactionLine_ID = rs.getInt(1);
				MAssetTransactionLine assetTransactionLine = new MAssetTransactionLine(m_ctx, iAssetTransactionLine_ID, get_TrxName());
				X_A_Asset asset = new X_A_Asset (getCtx(), assetTransactionLine.getA_Asset_ID(), get_TrxName());
				
				R = sheet.getRow(row);
				
				C = R.getCell(0);
				C.setCellValue(Integer.valueOf(nn));
				for(int i=0;i<2;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(0).getCellStyle());
				}
				
				C = R.getCell(2);
				C.setCellValue((asset.getName() == null ? " " : asset.getName() ));
				for(int i=2;i<11;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(2).getCellStyle());
				}
				
				C = R.getCell(11);
				C.setCellValue(asset.getAssetServiceDate() == null ? " " : new SimpleDateFormat("dd.MM.yyyy").format(asset.getAssetServiceDate()));
				for(int i=11;i<16;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(11).getCellStyle());
				}
				
				C = R.getCell(16);
				C.setCellValue(asset.getAssetServiceDate() == null ? " " : new SimpleDateFormat("dd.MM.yyyy").format(asset.getAssetServiceDate()));
				for(int i=16;i<21;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(16).getCellStyle());
				}
				
				C = R.getCell(21);
				C.setCellValue(Integer.valueOf(asset.getUseLifeMonths()));
				for(int i=21;i<27;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(21).getCellStyle());
				}
				
				C = R.getCell(27);
				C.setCellValue(assetTransactionLine.getAssetAmt().negate().doubleValue());
				for(int i=27;i<35;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(27).getCellStyle());
				}
				
				C = R.getCell(35);
				C.setCellValue(assetTransactionLine.getDeprAmt().negate().doubleValue());
				for(int i=35;i<41;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(35).getCellStyle());
				}
				
				C = R.getCell(41);
				BigDecimal depreciated_Cost = assetTransactionLine.getAssetAmt().negate().add(assetTransactionLine.getDeprAmt());
				total = total.add(depreciated_Cost);
				C.setCellValue(depreciated_Cost.doubleValue());
				for(int i=41;i<46;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(41).getCellStyle());
				}
				
				C = R.getCell(46);
				C.setCellValue(asset.getSerNo() == null ? " " : asset.getSerNo());
				for(int i=46;i<51;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(46).getCellStyle());
				}
				
				C = R.getCell(51);
				C.setCellValue(asset.getValue() == null ? " " : asset.getValue());
				for(int i=51;i<56;i++)
				{
					C = R.getCell(i);
					C.setCellStyle(sheet.getRow(25).getCell(51).getCellStyle());
				}
				
			}
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
		}
		
		R = sheet.getRow(row+1);
		C = R.getCell(41);
		C.setCellValue(total.toString());
		C = R.getCell(35);
		C.setCellValue("Итого:");
		
		fwMoney sumWords = new fwMoney(total.toString());
		R = sheet.getRow(row+45);
		C = R.getCell(13);
		C.setCellValue(sumWords.num2str());
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		try {
				//Forces Excel to use the specified file as a template for the default workbook. 
				Runtime.getRuntime().exec("cmd /c start excel " + file+" /t");
			} 
		catch (Exception e) 
			{
				e.printStackTrace();
			}
		
		return Msg.translate(m_ctx, "Success");
	}
		
}